<?php
// +----------------------------------------------------------------------
// | Desc: [ Better-充值审核 ]
// +----------------------------------------------------------------------
// | Author: huangzhen_20210406
// +----------------------------------------------------------------------

namespace app\common\model;
use think\facade\Db;
use app\api\controller\Index;

class CardsTransFz extends Common
{
    //---------------------------------------------后端---------------------------------------------------------------
    /**
     * 返回layui的table所需要的格式
     * @param $post
     * @param $user_id
     * @return mixed
     * @throws \think\db\exception\DbException
     */
    public function tableDataAdmin($post)
    {
        $list_rows  = $post['limit'] ?? config('paginate.list_rows');
        $page       = $post['page'] ?? config('paginate.page');

        $where =[];
        if(isset($post['user_name']) && !empty($post['user_name'])){
            $where[] = ['username', '=', trim($post['user_name'])];
        }
        if(isset($post['card_num']) && !empty($post['card_num'])){
            $where[] = ['ct.card_num', 'like', trim($post['card_num']) . "%"];
        }
        if(isset($post['shopping']) && !empty($post['shopping'])){
            $where[] = ['ct.shopping', 'like', "%" . trim($post['shopping']) . "%"];
        }
        if(isset($post['datetime']) && !empty($post['datetime'])){
            $time_list = explode(' _ ' , $post['datetime']);
            $start_time = $time_list[0] . " 00:00:00";
            $end_time   = $time_list[1] . " 23:59:59";
            $where[] = ['ct.ctime', 'between', "{$start_time},{$end_time}"];
        }

        $list = Db::name('cards_trans_fz')->alias('ct')
            ->leftJoin('user u' , 'u.id=ct.user_id')
            ->field('u.username,ct.*')
            ->order('ct.ctime desc')
            ->where($where)
            ->paginate(['list_rows' => $list_rows, 'page' => $page])
            ->toArray();
        return $result = [
            'code'  => 0,
            'msg'   => lang('get info success'),
            'data'  => $list['data'],
            'count' => $list['total'],
        ];
    }

    public function tableDataAdminTotal()
    {
        $fz_list = Db::name('cards_trans_fz')->select()->toArray();
        $list = [];
        foreach ($fz_list as $k=>$v){
            $card_num = $v['card_num'];
            $card_num_6 = substr($card_num , 0 ,6);
            $amount   = $v['amount'];
            $shopping = $v['shopping'];
            $shop_new = 'NULL';
            if(!empty($shopping)){
                $shopping_ex = explode(' ',$shopping);
                $shop_new    = $shopping_ex[0];
            }
            if(isset($list[$card_num_6])){
                $list[$card_num_6]['amount'] += $amount;
                $list[$card_num_6]['count'] += 1;
                if($list[$card_num_6]['amount_max'] < $amount){
                    $list[$card_num_6]['amount_max'] = $amount;
                }
                if(!in_array($card_num, $list[$card_num_6]['card_num'])){
                    $list[$card_num_6]['card_num'][] = $card_num;
                }
                if(isset($list[$card_num_6]['shop'][$shop_new])){
                    $list[$card_num_6]['shop'][$shop_new]['num'] += 1;
                    $list[$card_num_6]['shop'][$shop_new]['amount_sum'] += $amount;
                }else{
                    $list[$card_num_6]['shop'][$shop_new]['num'] = 1;
                    $list[$card_num_6]['shop'][$shop_new]['amount_sum'] = $amount;
                }
            }else{
                $list[$card_num_6]['amount'] = $amount;
                $list[$card_num_6]['count'] = 1;
                $list[$card_num_6]['amount_max'] = $amount;
                $list[$card_num_6]['card_num'][] = $card_num;
                $list[$card_num_6]['shop'][$shop_new]['num'] = 1;
                $list[$card_num_6]['shop'][$shop_new]['amount_sum'] = $amount;
            }
        }
        $return_list = [];
        $return_list1= [];
        $return_list2= [];
        foreach ($list as $kk=>$vv){
            $return_list1['amount'] = getSprintf(round($vv['amount'] , 2));
            $return_list1['count'] = $vv['count'];
            $return_list1['amount_max'] = getSprintf(round($vv['amount_max'] , 2));
            $return_list1['card_type'] = $kk;
            $return_list1['diff_card'] = count($vv['card_num']);
            $total_card_num = Db::name('cards')->where("LEFT(card_num, 6) = {$kk}")->count();
            $return_list1['total_num'] = $total_card_num;
            //获取卡强制消费 笔数最多的商家
            $shopping = $vv['shop'];
            $shopping2 = $shopping;
            $num_desc = array_column($shopping, 'num');
            array_multisort($num_desc, SORT_DESC, $shopping);
            $shop_num_max = 0;
            foreach ($shopping as $k_num => $v_num){
                if($k_num != "NULL"){
                    $shop_num_max = "({$v_num['num']}) " . $k_num;
                    break;
                }
            }
            //获取卡强制消费 金额最多的商家
            $amount_desc = array_column($shopping2, 'amount_sum');
            array_multisort($amount_desc, SORT_DESC, $shopping2);
            $shop_amount_max = 0;
            foreach ($shopping2 as $k_amount => $v_amount){
                if($k_amount != "NULL"){
                    $shop_amount_max = "(" . getSprintf(round($v_amount['amount_sum'] , 2)) . ") " .$k_amount;
                    break;
                }
            }
            $return_list1['shop_num_max'] = $shop_num_max;
            $return_list1['shop_amount_max'] = $shop_amount_max;

            $return_list[]=$return_list1;
        }
        return $result = [
            'code'  => 0,
            'msg'   => lang('get info success'),
            'data'  => $return_list,
            'count' => count($return_list),
        ];
    }

    public function tableDataAdminDec()
    {
        $list = Db::name('cards_trans')
            ->where([
                'shopping'      =>'AMZ*Amazon Payments',
                'tran_status'   => 'Declined',
                'bank_type'     => 3
            ])
            ->select()->toArray();

        $result_list = [];
        foreach ($list as $k=>$v){
            $user_id = $v['user_id'];
            if(isset($result_list[$user_id])){
                $result_list[$user_id]['num'] += 1;
                $result_list[$user_id]['amount_auth'] += $v['amount_auth'];
            }else{
                if(substr($v['card_num'],0,6) == 559666){
                    $result_list[$user_id]['is_use'] = 1;
                }else{
                    $result_list[$user_id]['is_use'] = 0;
                }
                $result_list[$user_id]['num'] = 1;
                $result_list[$user_id]['amount_auth'] = $v['amount_auth'];
            }
        }

        $return_list =[];
        $return_list1=[];

        foreach ($result_list as $kk=>$vv){
            $user_list = Db::name('user')->find($kk);
            $return_list1['username'] = $user_list['username'];
            $return_list1['balance'] = $user_list['balance'];

            //客户总充值金额
            $fund_credit = Db::name('fund_check')->field("sum(amount) amount")
                        ->where("check_status = 1 and type = 0 and user_id = {$kk}")
                        ->sum('amount');
            $fund_debit = Db::name('fund_check')->field("sum(amount) amount")
                ->where("check_status = 1 and type = 1 and user_id = {$kk}")
                ->sum('amount');
            $return_list1['fund_amount'] = $fund_credit - $fund_debit;

            //卡总余额
            $card_balance  = Db::name('cards')->where(['user_id'=>$kk])
                ->sum('balance');
            $return_list1['card_balance'] = $card_balance;

            $return_list1['amount_auth'] = getSprintf(round($vv['amount_auth'],2));
            $return_list1['num'] = $vv['num'];
            $return_list1['is_use'] = $vv['is_use'];
            $return_list[] = $return_list1;
        }

        return $result = [
            'code'  => 0,
            'msg'   => lang('get info success'),
            'data'  => $return_list,
            'count' => count($return_list),
        ];
    }
}